package com.lbj.dao.user;

import com.lbj.bean.Role;
import com.lbj.bean.User;
import com.lbj.dao.BaseDao;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    @Override
    public User getUser(Connection connection, String userCode) throws SQLException {
        PreparedStatement statement = null;
        ResultSet set = null;
        User user = null;
        if (connection != null) {
            String sql = "select * from smbms_user where userCode=?";
            Object[] params = {userCode};
            set = BaseDao.execute(connection, statement, set, sql, params);
            if (set != null && set.next()) {
                user = new User();
                user.setId(set.getInt("id"));
                user.setUserCode(set.getString("userCode"));
                user.setUserName(set.getString("userName"));
                user.setUserPassword(set.getString("userPassword"));
                user.setGender(set.getInt("gender"));
                user.setBirthday(set.getDate("birthday"));
                user.setPhone(set.getString("phone"));
                user.setAddress(set.getString("address"));
                user.setUserRole(set.getInt("userRole"));
            }
            BaseDao.release(null, null, set);
        }
        return user;
    }

    @Override
    public int updatePwd(Connection connection, String userId, String password) throws SQLException {
        PreparedStatement statement = null;
        String sql = "update smbms_user set userPassword = ? where userCode = ?";
        int execute = BaseDao.execute(connection, statement, sql, new Object[]{password, userId});
        BaseDao.release(null, statement, null);
        return execute;
    }

    /**
     * select count(1) count from smbms_user user,smbms_role role
     * where user.userRole = role.id
     * and user.userName like '%test%'
     * and user.userRole = 1
     */
    @Override
    public int getUserCount(Connection connection, String username, int userRole) throws SQLException {
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        // 注意这里的sql
        String sql = "select count(1) count from smbms_user user,smbms_role role where user.userRole = role.id";
        StringBuilder builder = new StringBuilder();
        builder.append(sql);
        List<Object> params = new ArrayList<>();

        // 用户名不为空时，搜索用户名
        if (!StringUtils.isNullOrEmpty(username)) {
            builder.append(" and user.userName like ?");
            params.add("%" + username + "%");
        }

        if (userRole > 0) {
            builder.append(" and user.userRole = ?");
            params.add(userRole);
        }

        String newSql = builder.toString();
        System.out.println("最终查询SQL：" + newSql);

        resultSet = BaseDao.execute(connection, statement, resultSet, newSql, params.toArray());

        int count = 0;

        if (resultSet.next()) {
            count = resultSet.getInt("count");
        }

        BaseDao.release(null, statement, resultSet);

        return count;
    }

    /**
     * select * from smbms_user user,smbms_role role where user.userRole = role.id
     * and user.userName like '%test%'
     * and user.userRole = 1
     * order by userCode DESC limit 5,5
     */
    @Override
    public List<User> getUserList(Connection connection, String username, int userRole, int index, int pageSize) throws SQLException {
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        String sql = "select * from smbms_user user,smbms_role role where user.userRole = role.id";

        StringBuilder builder = new StringBuilder();
        List<Object> params = new ArrayList<>();

        builder.append(sql);
        if (!StringUtils.isNullOrEmpty(username)) {
            builder.append(" and user.userName like ?");
            params.add("%" + username + "%");
        }
        if (userRole > 0) {
            builder.append(" and user.userRole = ?");
            params.add(userRole);
        }

        builder.append(" order by userCode DESC limit ?,?");
        params.add(index);
        params.add(pageSize);

        String newSql = builder.toString();
        System.out.println("最终SQL：" + newSql);

        resultSet = BaseDao.execute(connection, statement, resultSet, newSql, params.toArray());

        List<User> userList = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt("id"));
            user.setUserCode(resultSet.getString("userCode"));
            user.setUserName(resultSet.getString("userName"));
            user.setUserPassword(resultSet.getString("userPassword"));
            user.setGender(resultSet.getInt("gender"));
            user.setBirthday(resultSet.getDate("birthday"));
            user.setPhone(resultSet.getString("phone"));
            user.setAddress(resultSet.getString("address"));
            user.setUserRole(resultSet.getInt("userRole"));
            userList.add(user);
        }
        return userList;
    }
}
